﻿namespace jzgl.Controller
{
    using jzgl.Model;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Windows.Forms;

    public class BudgetListDB
    {
        private SqlConnection conn = null;
        private ConnDB conndb = new ConnDB();
        private DataTable table = null;

        public BudgetListDB()
        {
            this.conn = this.conndb.GetConnString();
        }

        public int Add_BudgetList(BudgetList list)
        {
            int scalar = 0;
            try
            {
                try
                {
                    string sql = "insert into BudgetList(BudListKey,CustName,CustPhone,Designer,DesPic,HouseType,HouseArea,DesStyle,DesDeposit,CustFrom,BudAddress,BudTotal,BudgetTime,BudRemark) values(@BudListKey,@CustName,@CustPhone,@Designer,@DesPic,@HouseType,@HouseArea,@DesStyle,@DesDeposit,@CustFrom,@BudAddress,@BudTotal,@BudgetTime,@BudgetRemark)";
                    SqlParameter[] values = new SqlParameter[] { new SqlParameter("@BudListKey", list._BudListKey), new SqlParameter("@CustName", list._CustName), new SqlParameter("@CustPhone", list._CustPhone), new SqlParameter("@Designer", list._Designer), new SqlParameter("@DesPic", list._DesPic), new SqlParameter("@HouseType", list._HourseType), new SqlParameter("@HouseArea", list._HourseArea), new SqlParameter("@DesStyle", list._DesStyle), new SqlParameter("@DesDeposit", list._DesDeposit), new SqlParameter("@CustFrom", list._CustFrom), new SqlParameter("@BudAddress", list._BudAddress), new SqlParameter("@BudTotal", list._BudTotal), new SqlParameter("@BudgetTime", list._BudgetTime), new SqlParameter("@BudgetRemark", list._BudRemark) };
                    scalar = this.conndb.GetScalar(sql, values);
                }
                catch (Exception exception)
                {
                    Console.WriteLine(exception.Message);
                }
            }
            finally
            {
            }
            return scalar;
        }

        public int Delete_BudgetList(BudgetList list)
        {
            int num = 0;
            try
            {
                try
                {
                    string sql = "delete from BudgetList where BudListKey='" + list._BudListKey + "'";
                    num = this.conndb.Execute_Sql(sql);
                }
                catch (Exception)
                {
                }
            }
            finally
            {
            }
            return num;
        }

        public int Delete_BudgetList(string listId)
        {
            int num = 0;
            try
            {
                try
                {
                    string sql = "delete from BudgetList  where  BudListKey='" + listId + "'";
                    num = this.conndb.Execute_Sql(sql);
                }
                catch (Exception)
                {
                }
            }
            finally
            {
            }
            return num;
        }

        public DataTable Show_BudgetList()
        {
            try
            {
                try
                {
                    string sql = "select * from BudgetList";
                    this.table = this.conndb.FillDataTable(sql);
                }
                catch (Exception)
                {
                }
            }
            finally
            {
            }
            return this.table;
        }

        public void Show_BudgetList(DataGridView dgv)
        {
            try
            {
                try
                {
                    string sqlstr = "select * from BudgetList";
                    this.conndb.dgvDataBind(dgv, sqlstr);
                }
                catch (Exception)
                {
                }
            }
            finally
            {
            }
        }

        public DataTable Show_BudgetListBy_BudlistId(string budlistId)
        {
            try
            {
                try
                {
                    string sql = "select * from v_budgetListInfo where BudListKey='" + budlistId + "'";
                    this.table = this.conndb.FillDataTable(sql);
                }
                catch (Exception)
                {
                }
            }
            finally
            {
            }
            return this.table;
        }

        public DataTable Show_BudgetListBy_BudlistId(string uId, string budlistId)
        {
            try
            {
                try
                {
                    string sql = "select * from v_budgetListInfo where BudListKey='" + budlistId + "' and  BudRemark='" + uId + "'";
                    this.table = this.conndb.FillDataTable(sql);
                }
                catch (Exception)
                {
                }
            }
            finally
            {
            }
            return this.table;
        }

        public DataTable Show_BudgetListBy_Designer(string desginer)
        {
            try
            {
                try
                {
                    string sql = "select * from BudgetList where Designer like '%" + desginer + "%'";
                    this.table = this.conndb.FillDataTable(sql);
                }
                catch (Exception)
                {
                }
            }
            finally
            {
            }
            return this.table;
        }

        public DataTable Show_BudgetListBy_Designer(string uId, string desginer)
        {
            try
            {
                try
                {
                    string sql = "select * from BudgetList where Designer like '%" + desginer + "%' and BudRemark='" + uId + "'";
                    this.table = this.conndb.FillDataTable(sql);
                }
                catch (Exception)
                {
                }
            }
            finally
            {
            }
            return this.table;
        }

        public DataTable Show_BudgetListBy_Designer2(string uId)
        {
            try
            {
                try
                {
                    string sql = "select * from BudgetList where BudRemark='" + uId + "'";
                    this.table = this.conndb.FillDataTable(sql);
                }
                catch (Exception)
                {
                }
            }
            finally
            {
            }
            return this.table;
        }

        public DataTable Show_BudgetListBy_DesStyle(string style)
        {
            try
            {
                try
                {
                    string sql = "select * from BudgetList where DesStyle like '%" + style + "%'";
                    this.table = this.conndb.FillDataTable(sql);
                }
                catch (Exception)
                {
                }
            }
            finally
            {
            }
            return this.table;
        }

        public DataTable Show_BudgetListBy_DesStyle(string uId, string style)
        {
            try
            {
                try
                {
                    string sql = "select * from BudgetList where DesStyle like '%" + style + "%' and BudRemark='" + uId + "'";
                    this.table = this.conndb.FillDataTable(sql);
                }
                catch (Exception)
                {
                }
            }
            finally
            {
            }
            return this.table;
        }

        public int Update_BudgetList(BudgetList list)
        {
            int num = 0;
            try
            {
                try
                {
                    string sql = string.Concat(new object[] { 
                        "update BudgetList set CustName='", list._CustName, "',CustPhone='", list._CustPhone, "',Designer='", list._Designer, "',DesPic='", list._DesPic, "',HouseType='", list._HourseType, "',HouseArea='", list._HourseArea, "',DesStyle='", list._DesStyle, "',DesDeposit='", list._DesDeposit, 
                        "',BudgetTime='", list._BudgetTime, "',CustFrom='", list._CustFrom, "',BudAddress='", list._BudAddress, "',BudSpecial='", list._BudSpecial, "',BudTotal='", list._BudTotal, "', BudRemark='", list._BudRemark, "'  where  BudListKey='", list._BudListKey, "'"
                     });
                    num = this.conndb.Execute_Sql(sql);
                }
                catch (Exception)
                {
                }
            }
            finally
            {
            }
            return num;
        }

        public int Update_BudgetList_DesName(string des_name, string des)
        {
            int num = 0;
            try
            {
                try
                {
                    string sql = "update BudgetList set Designer='" + des_name + "'    where  Designer='" + des + "'";
                    num = this.conndb.Execute_Sql(sql);
                }
                catch (Exception)
                {
                }
            }
            finally
            {
            }
            return num;
        }
    }
}

